/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 *
 * @author issan
 */
public class Query_Docente {
      
    private static HashMap docenti;
    
    public static Object query_dati_docente(String nome, int id){
        

       if(docenti==null){
            docenti = new HashMap(150);  
        }
        if(docenti.containsKey(nome)){
            return docenti.get(nome);
        }

        Docente x = new Docente();
       
       String query = ("Select * from ( ("
          + "   (SELECT professor as prof,place,actual_start_date,url,course_name,credits,planned_course_period,notes,course_ID FROM courses ) as cou "
          + "  RIGHT JOIN "
          + " ( SELECT supervisor_ID as prof,first_name,last_name,email,can_be_advisor FROM supervisors where supervisor_ID = '"+id+"' )as pro ON cou.prof=pro.prof "
          +  " ) LEFT JOIN "
          +   " (SELECT course,student,is_finished,updated_at,qualification FROM courses_enrollments ) as iscrizioni ON iscrizioni.course=cou.course_ID "
          + " ) LEFT JOIN "
          + " ( SELECT user_ID ,first_name,last_name FROM students ) as stud ON stud.user_ID = iscrizioni.student  ORDER BY cou.course_ID ");
       
       try 
       {
        Connection con = DBconnection.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
       
        
       Corsi_tenuti_docente corso;   
       Studenti_Corsi_Iscrizioni studente = null;

      while ( rs.next()) {
            x.setMatricola(rs.getString("pro.prof"));
            x.setNome(rs.getString("pro.first_name"));
            x.setCognome(rs.getString("pro.last_name"));  
            x.setCan_be_advisor(rs.getBoolean("pro.can_be_advisor"));
            
            corso = x.getCorso(rs.getInt("cou.course_ID"));
            if(corso == null){
                corso = new Corsi_tenuti_docente();
                corso.setId(rs.getInt("cou.course_ID"));
                corso.setNome(rs.getString("cou.course_name"));
                corso.setAula_lezioni(rs.getString("cou.place")); 
                corso.setData_inizio(rs.getString("cou.actual_start_date"));
                corso.setDocente(rs.getString("pro.first_name")+" "+rs.getString("pro.last_name"));
                corso.setEmail(rs.getString("pro.email"));
                corso.setUrl_corso(rs.getString("cou.url"));
                corso.setNome(rs.getString("cou.course_name"));
                corso.setCrediti(rs.getString("cou.credits"));
                corso.setPeriodo(rs.getString("cou.planned_course_period"));
                corso.setNote(rs.getString("cou.notes"));   
            }
          
             
            
            studente = new Studenti_Corsi_Iscrizioni();
            studente.setMatricola(rs.getString("stud.user_ID"));
            studente.setNome(rs.getString("stud.first_name"));
            studente.setCognome(rs.getString("stud.last_name"));    
            studente.setCrediti(rs.getString("cou.credits"));
            studente.setData_appello(rs.getDate("iscrizioni.updated_at"));
            
            
            if(rs.getString("iscrizioni.is_finished") == null){
                studente.setIs_finished(false);
            }else{
                studente.setIs_finished(rs.getBoolean("iscrizioni.is_finished"));
            }
            

            studente.setVoto(rs.getString("iscrizioni.qualification"));
            if(studente.getMatricola()!=null)
                corso.aggiungiStudente(Integer.parseInt(studente.getMatricola()), studente);      
            corso.setNumero_iscritti(String.valueOf(corso.getStudenti().size()));
            if(corso.getId()!=0)    
                x.aggiungiCorso(rs.getInt("cou.course_ID"), corso);
      
     
            
            } 
            con.close();
       }
       catch(Exception ex){
           
           ex.printStackTrace();
           
       }

       docenti.put(nome, x);
     return x;
    } 
    
    
    
    
    public static boolean query_inserisci_voto(String nome ,int id_corso, int id_studente,String approvato, String voto){
        
        Docente docente  = (Docente) docenti.get(nome);
        Corsi_tenuti_docente corso = docente.getCorso(id_corso);
        if(corso==null)
            return false;
        Studenti_Corsi_Iscrizioni studente = corso.getStudente(id_studente);
        if(studente==null)
            return false;         
        
        if(approvato.contentEquals("approvato")){
            Date now = new Date();
            SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            approvato = formato.format(now);
        }else{
            approvato = null;
        }
        
        // STRTTURA DINAMICA
         docente.getCorso(id_corso).getStudente(id_studente).setIs_finished(true);
         docente.getCorso(id_corso).getStudente(id_studente).setVoto(voto);
         
         
        // DATABSE
        String query = "UPDATE courses_enrollments SET is_finished=1, updated_at='"+approvato+"' ,qualification= '"+voto+"' WHERE student="+id_studente+" AND course="+id_corso+";";
        try 
        {
            Connection con = DBconnection.getConnection();
            Statement stmt = con.createStatement();
            stmt.executeUpdate(query);
            con.close();
        }
        catch(Exception ex){
           
           ex.printStackTrace();
           return false;
        }
        
        studente.setVoto(voto);
        
        return true;
    }
    
    
    
    
    
            public static void delete(String user)
                {
                    if(docenti != null)
                        if(docenti.containsKey(user))
                            docenti.remove(user);
                }
    
    
}
